﻿---------------------------------------------------------------------------------------------------------------
-- Delete any existing ReturnDetail records for this RMA and create new ReturnDetail records based on the
-- InventoryTransaction records tied to the RMA.
---------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[spReconcileReturnDetail]
	@rmaReturnID int
AS
	set nocount on
	delete from tblReturnDetail where RMAReturnID = @rmaReturnID;

	declare cProducts cursor local for
	select T2.ProductID, T2.Qty, T2.OrderDetailID
	from tblRMAReturn T1
		join tblOrderDetail T2 on T1.OrderID = T2.OrderID
	where T1.RMAReturnID = @rmaReturnID
	declare @ProductID int
	declare @Qty int
	declare @OrderDetailID int
	open cProducts
	fetch next from cProducts into @ProductID, @Qty, @OrderDetailID
	while @@FETCH_STATUS = 0
	begin
		-- Create new ReturnDetail records for each matching InventoryTransaction return record
		insert into tblReturnDetail ( RMAReturnID, Qty, OrderDetailID )
		select @rmaReturnID, T3.Qty / T1.Qty, @OrderDetailID
		from dbo.ProductBuildPlan(@ProductID) T1
			join tblInventory T2 on T1.ProductID = T2.ProductID
			join tblInventoryTransaction T3 on T2.InventoryID = T3.InventoryID
				and T3.RMAReturnID = @rmaReturnID
				and T3.InventoryTransactionTypeCode in ( 'E', 'U' )
				and T3.InventoryTransactionStatusCode = 'C'
		where T3.Qty / T1.Qty > 0

		fetch next from cProducts into @ProductID, @Qty, @OrderDetailID
	end
	close cProducts
	deallocate cProducts
RETURN 0